const db = require("../config/db");

class Review {
  static async searchReviews(content, begin, end, page, limit) {
    try {
      const offset = (page - 1) * limit;
      const whereConditions = [];
      const queryParams = [];
      // 构建模糊查询条件
      if (content) {
        whereConditions.push("reviews.content LIKE ?");
        queryParams.push(`%${content}%`);
      }
      // 构建时间范围查询
      if (begin && end) {
        whereConditions.push("reviews.created_at BETWEEN ? AND ?");
        queryParams.push(...[begin, end]);
      } else {
        if (begin) whereConditions.push("reviews.created_at >= ?");
        if (end) whereConditions.push("reviews.created_at <= ?");
        if (begin) queryParams.push(begin);
        if (end) queryParams.push(end);
      }
      // 组合WHERE子句
      const whereClause =
        whereConditions.length > 0
          ? `WHERE ${whereConditions.join(" AND ")}`
          : "";
      // 主查询（带分页）
      const dataQuery = `
        SELECT 
          reviews.id AS review_id,
          movies.title AS movie_name,
          reviews.content,
          reviews.created_at,
          users.username,
          reviews.user_rating,
          reviews.likes
        FROM reviews
        INNER JOIN movies ON reviews.movie_id = movies.id
        INNER JOIN users ON reviews.user_id = users.id
        ${whereClause}
        ORDER BY reviews.created_at DESC
        LIMIT ? OFFSET ?
      `;
      // 总数查询
      const countQuery = `
        SELECT COUNT(*) AS total
        FROM reviews
        INNER JOIN movies ON reviews.movie_id = movies.id
        INNER JOIN users ON reviews.user_id = users.id
        ${whereClause}
      `;
      // 执行查询
      const [dataRows] = await db.query(dataQuery, [
        ...queryParams,
        limit,
        offset,
      ]);
      const [totalRows] = await db.query(countQuery, queryParams);
      return {
        total: totalRows[0].total,
        page: parseInt(page),
        limit: parseInt(limit),
        data: dataRows,
      };
    } catch (error) {
      throw new Error(`搜索失败: ${error.message}`);
    }
  }

  static async findAllByMovieId(
    movieId,
    sortType = "created_at",
    page = 1,
    limit = 10
  ) {
    page = Number(page);
    limit = Number(limit);
    const offset = (page - 1) * limit;
    const orderBy =
      {
        hot: "r.likes DESC",
        created_at: "r.created_at DESC",
      }[sortType] || "r.created_at DESC";
    const [rows] = await db.query(
      `
    SELECT 
      r.*,
      u.username 
    FROM reviews r
    LEFT JOIN users u ON r.user_id = u.id
    WHERE r.movie_id = ?
    ORDER BY ${orderBy}
    LIMIT ? OFFSET ?
  `,
      [movieId, limit, offset]
    );
    return rows;
  }

  static async findById(id) {
    const [rows] = await db.query("SELECT * FROM reviews WHERE id = ?", [id]);
    return rows.length ? rows[0] : null;
  }

  static async findWithPaginationAndSort(
    page = 1,
    pageSize = 10,
    sort = "likes",
    order = "DESC"
  ) {
    // 添加参数校验
    page = Math.max(parseInt(page) || 1, 1);
    pageSize = Math.min(Math.max(parseInt(pageSize) || 10, 1), 100);
    const validSortFields = ["likes", "created_at", "user_rating"];
    sort = validSortFields.includes(sort) ? sort : "likes";
    order = ["ASC", "DESC"].includes(order.toUpperCase()) ? order : "DESC";
    // 计算分页的偏移量
    const offset = (page - 1) * pageSize;

    // 查询总记录数
    const [totalResult] = await db.query(
      "SELECT COUNT(*) as total FROM reviews"
    );
    const total = totalResult[0].total;

    // 查询分页数据，包含用户名和电影封面
    const [rows] = await db.query(
      `SELECT reviews.*, users.username, movies.title, movies.poster_url FROM reviews 
         JOIN users ON reviews.user_id = users.id 
         JOIN movies ON reviews.movie_id = movies.id
         ORDER BY ${sort} ${order} 
         LIMIT ? OFFSET ?`,
      [pageSize, offset]
    );
    // 计算总页数
    const totalPages = Math.ceil(total / pageSize);
    return {
      data: rows,
      total,
      totalPages,
      currentPage: page,
      pageSize,
    };
  }

  static async create(review) {
    const [result] = await db.query(
      "INSERT INTO reviews (movie_id, user_id, user_rating, content) VALUES (?, ?, ?, ?)",
      [review.movie_id, review.user_id, review.user_rating, review.content]
    );
    return result.insertId;
  }

  static async delete(id) {
    await db.query("DELETE FROM reviews WHERE id = ?", [id]);
    return true;
  }

  static async update(id, review) {
    // 当前时间，用于更新update_time
    const update_time = new Date().toISOString().slice(0, 19).replace("T", " ");
    await db.query(
      "UPDATE reviews SET user_id = ?, user_rating = ?, content = ?, update_time = ? WHERE id = ?",
      [
        review.user_id,
        review.user_rating,
        review.content,
        update_time,
        id,
      ]
    );
    return true;
  }

  static async incrementLikes(id) {
    await db.query("UPDATE reviews SET likes = likes + 1 WHERE id = ?", [id]);
    return this.findById(id); // 返回更新后的数据
  }
}

module.exports = Review;
